import pymysql


class simple_sql:
    # 初始化 创建数据库 创建数据表

    def __init__(self,HOST,USER,PASSWORD):
        try:
            """user 为你自己的mysql数据库用户的账号  password 为密码  database为数据库的名字"""
            self.conn = pymysql.connect(
                host = HOST,
                user = USER, password = PASSWORD,
                charset = 'utf8')
            self.cursor = self.conn.cursor()
            print('数据库连接成功')
            # self.create_database()
            self.connect_database()
        except Exception as e:
            print('程序出错', e)

    def init_sql(self):
        self.create_request_table()
        self.create_state_table()
        self.create_diary_table()

    def create_database(self):
        try:
            self.cursor.execute('create database if not exists sqldb')
            print('数据库创建成功')
        except Exception as e:
            print('程序出错', e)

    def connect_database(self):
        try:
            self.conn.select_db('sqldb')
            print('数据库连接成功')
        except Exception as e:
            print('程序出错', e)

    def create_request_table(self):
        try:
            sql = f"""
            CREATE TABLE IF NOT EXISTS db_request (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            request CHAR(20) NOT NULL
            )ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
            """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def create_state_table(self):
        try:
            sql = f"""
                    CREATE TABLE IF NOT EXISTS state (
                    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    room_id CHAR(20) NOT NULL,
                    room_state INT NOT NULL,
                    ac_status CHAR(20) NOT NULL,
                    operate CHAR(20) NOT NULL,
                    time CHAR(20) NOT NULL,
                    mode CHAR(20) NULL,
                    set_tmp INT NULL,
                    wind_spd INT NULL,
                    wind_drc INT NULL,
                    sweep_mode INT NULL,
                    error_detail CHAR(20) NULL
                    )ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
                    """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def create_diary_table(self):
        try:
            sql = f"""
                    CREATE TABLE IF NOT EXISTS db_log (
                    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    time CHAR(20) NOT NULL,
                    operate CHAR(20) NOT NULL
                    )ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
                    """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def insert_request(self, request):
        try:
            sql = f"""
                    INSERT INTO db_request (request) VALUES ('{request}')
                    """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def insert_state(self, room_id, room_state, ac_status, operate, time, mode, set_tmp, wind_spd, wind_drc, sweep_mode,
                     error_detail):
        try:
            sql = f"""
                    INSERT INTO state (room_id, room_state, ac_status, operate, time, mode, set_tmp, wind_spd, wind_drc, sweep_mode, error_detail)
                    VALUES ('{room_id}', '{room_state}', '{ac_status}', '{operate}', '{time}', '{mode}', 
                    '{set_tmp}', '{wind_spd}', '{wind_drc}', '{sweep_mode}', '{error_detail}')
                    """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def insert_diary(self, time, operate):
        try:
            sql = f"""
                    INSERT INTO db_log (time, operate)
                    VALUES ('{time}', '{operate}')
                    """
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('程序出错', e)

    def select_status(self):
        # 待实现，将下面sql语句实现,id为主键，speed为风速,amount为费用,target_temp为目标温度,cur_temp为当前温度
        # 返回值为列表，列表中的元素为五元组，分别为id,target_temp,cur_temp,speed,amount
        # self.cursor.execute("select id,target_temp,cur_temp,speed, amount from status where speed!=0")
        pass
    
    def status_fee(self,id,cost):
        # self.cursor.execute("update status set amount=amount+%s where id=%s"%(cost,status['id'][num]))
        pass

    def close_sql(self):
        try:
            self.cursor.close()
            self.conn.close()
        except Exception as e:
            print('程序出错', e)


if __name__ == '__main__':
    sql = simple_sql('localhost','root','123456')
    sql.create_database()
    sql.connect_database()
    sql.init_sql()
    # sql.insert_request('test')
    # sql.insert_state('1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1')
    # sql.insert_diary('1', '1')
    sql.close_sql()
